#Region "Import Section"
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
Imports System.Configuration
Imports MyClasses
Imports DAL
Imports Excel
'Imports System.Runtime.InteropServices.Marshal
#End Region

Public Class MasterImport

#Region "Declaration"
    Dim dsRecord As DataSet
    Dim PrevSheet As String = ""
    Dim ExcelFilePath As String = ""
    'Dim intChkFlag As Integer

    Dim MyConnection As OleDbConnection
    Dim MyAdapter As OleDbDataAdapter
    Dim frmPrg As New frmProgressBar
    'Dim SqlCommand As SqlCommand
    'Dim SqlConnection As SqlConnection
    'Dim strConString As String = "server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Password=" & Trim(ConfigurationManager.AppSettings("PWD")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
#End Region

#Region "Key Up Events"

    Private Sub MasterImport_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyUp
        If e.KeyCode = Keys.F1 Then
            e.SuppressKeyPress = True
            Help.ShowHelp(Me, "Pharma.chm", HelpNavigator.KeywordIndex, "Master Import")
        End If
    End Sub
    Private Sub txtFilePath_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtFilePath.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub btnOpenFile_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles btnOpenFile.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub cmbSheet_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cmbSheet.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub Rdodiscard_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Rdodiscard.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub Rdoupdate_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Rdoupdate.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub btnPreview_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles btnPreview.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

    Private Sub btnImports_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles btnImports.KeyDown
        If e.KeyCode = Keys.Enter Then SendKeys.Send("{TAB}")
    End Sub

#End Region

#Region "Form Control Event"

    Private Sub MasterImport_HelpButtonClicked(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles Me.HelpButtonClicked
        Help.ShowHelp(Me, "Pharma.chm", HelpNavigator.KeywordIndex, "Master Import")
        e.Cancel = True
    End Sub

    Private Sub MasterImport_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            BCommon.SetFormStandards(Me)
            Rdodiscard.Checked = True
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "MasterImport_Load")
        End Try
    End Sub

    Private Sub btnOpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenFile.Click
        Dim objOpen As New OpenFileDialog
        Try

            objOpen.Title = "Open Excel File"
            objOpen.Filter = "Excel Files|*.xls"

            If objOpen.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                cmbSheet.Items.Clear()
                Me.txtFilePath.Text = objOpen.FileName()
                'Read All Excel WorkSheet from the FILE
                If Me.txtFilePath.Text <> "" Then
                    Call ReadExcelSheet()
                End If
                btnImports.Enabled = True
            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "btnOpenFile_Click")
        End Try
    End Sub

    Private Sub cmbSheet_DropDownClosed(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbSheet.DropDownClosed
        Try
            If cmbSheet.Text <> "" Then
                PrevSheet = Trim(cmbSheet.Text)
                If Trim(cmbSheet.Text) = "OpeningStock" Then Rdoupdate.Enabled = False Else Rdoupdate.Enabled = True
            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "cmbSheet_DropDownClosed")
        End Try
    End Sub

    Private Sub btnPreview_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPreview.Click
        Try
            If cmbSheet.SelectedIndex <= 0 Then BCommon.ShowMsgError(10422) : Exit Sub
            frmPrg.TopMost = True
            frmPrg.MaximumValue = 10
            'frmPrg.lblProgrsBar.Text = "Please Wait while Previwing Data"
            frmPrg.Show()
            frmPrg.SetProgress(1)
            Call ReadExcelData()
            If dsRecord.Tables(0).Rows.Count < 1 Then frmPrg.Hide() : BCommon.ShowMsgError(10433) : Exit Sub
            frmPrg.SetProgress(7)
            frmPrg.SetProgress(10)
            frmPrg.Hide()
            Dim FrmMasterDataPreview As New frmMasterPreview
            With FrmMasterDataPreview
                '.strFilePath = Trim(txtFilePath.Text.ToString())
                .strPrevSheet = Trim(cmbSheet.SelectedItem.ToString())
                .dsPrevRecord = dsRecord
                .ByteClick = 1
                .ShowDialog()
            End With
            frmPrg.Hide()
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "btnPreview_Click")
        End Try
    End Sub

    Private Sub btnImports_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImports.Click
        Try
            If txtFilePath.Text = String.Empty Then
                BCommon.ShowMsgError(10417)
            ElseIf cmbSheet.SelectedIndex <= 0 Then
                BCommon.ShowMsgError(10422)
            ElseIf cmbSheet.Text = "Manufacturer" Then
                btnImports.Enabled = False
                frmPrg.TopMost = True
                frmPrg.MaximumValue = 10
                frmPrg.Show()
                frmPrg.SetProgress(1)

                Call ReadExcelData()
                If dsRecord.Tables(0).Rows.Count < 1 Then BCommon.ShowMsgError(10436) : frmPrg.Hide() : Exit Sub
                Call StoreManufacturer(dsRecord)
            ElseIf cmbSheet.Text = "Vendor" Then
                btnImports.Enabled = False
                frmPrg.TopMost = True
                frmPrg.MaximumValue = 10
                frmPrg.Show()
                frmPrg.SetProgress(1)

                Call ReadExcelData()
                If dsRecord.Tables(0).Rows.Count < 1 Then BCommon.ShowMsgError(10436) : frmPrg.Hide() : Exit Sub
                Call StoreVendor(dsRecord)
            ElseIf cmbSheet.Text = "ItemMaster" Then
                btnImports.Enabled = False
                frmPrg.TopMost = True
                frmPrg.MaximumValue = 10
                frmPrg.Show()
                frmPrg.SetProgress(1)

                Call ReadExcelData()
                If dsRecord.Tables(0).Rows.Count < 1 Then BCommon.ShowMsgError(10436) : frmPrg.Hide() : Exit Sub
                Call StoreItemMaster(dsRecord)
            ElseIf cmbSheet.Text = "OpeningStock" Then
                btnImports.Enabled = False
                frmPrg.TopMost = True
                frmPrg.MaximumValue = 10
                frmPrg.Show()
                frmPrg.SetProgress(1)

                Call ReadExcelData()
                If dsRecord.Tables(0).Rows.Count < 1 Then BCommon.ShowMsgError(10436) : frmPrg.Hide() : Exit Sub
                Call StoreOpenStock(dsRecord)
            Else
                BCommon.ShowMsgError(10435)
            End If
            frmPrg.Hide()

            btnImports.Enabled = True
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "btnImports_Click")
        End Try
    End Sub

    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

#End Region

#Region "User Defined Function"

    'Read the Excel Data from the specified path
    Private Sub ReadExcelSheet()
        Dim strSheet As String = String.Empty
        'Dim bytsheet As Byte
        'Try
        '    'intChkFlag = 0
        '    Dim intSheet As Integer = 0
        '    Dim xlsApp As Excel.Application
        '    Dim xlsWB As Excel.Workbook
        '    Dim xlsSheet As Excel.Worksheet
        '    'Dim xlsCell As Excel.Range

        '    xlsApp = New Excel.Application
        '    'xlsApp.Visible = True
        '    xlsWB = xlsApp.Workbooks.Open(Trim(Me.txtFilePath.Text))
        '    cmbSheet.Items.Add("Select a sheet")
        '    cmbSheet.SelectedIndex = 0
        '    For intSheet = 1 To xlsWB.Sheets.Count
        '        xlsSheet = xlsWB.Sheets.Item(intSheet)
        '        strSheet = xlsSheet.Name
        '        If strSheet <> "Help" Then cmbSheet.Items.Add(strSheet) Else bytsheet = 1
        '    Next
        '    If bytsheet = 0 Then BCommon.ShowMsgError(10434) : cmbSheet.Items.Clear() : txtFilePath.Clear() : Exit Sub
        '    If cmbSheet.Items.Count > 0 Then btnPreview.Enabled = True
        '    'intChkFlag = 1
        '    xlsApp.Workbooks.Close()
        'Catch ex As Exception
        '    BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "ReadExcelSheet")
        'End Try
    End Sub

    'Read the Excel Data from the specified path
    Private Sub ReadExcelData()
        Try
            frmPrg.SetProgress(3)
            ExcelFilePath = Trim(Me.txtFilePath.Text)   '//Excel file path
            PrevSheet = Trim(cmbSheet.SelectedItem.ToString())
            MyConnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & ExcelFilePath & "; Extended Properties=Excel 8.0 ; ")
            Dim query = "SELECT * FROM [" & PrevSheet & "$]"
            MyAdapter = New OleDbDataAdapter(query, MyConnection)
            MyAdapter.TableMappings.Add("Table", "ExcelSheet")
            dsRecord = New DataSet
            frmPrg.SetProgress(4)
            MyAdapter.Fill(dsRecord)
            MyConnection.Close()
            frmPrg.SetProgress(6)
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "ReadExcelData")
            MyConnection.Close()
        End Try
    End Sub

    'Store the Manufacturer Data from ExcelSheet to SQL DATABASE
    Private Sub StoreManufacturer(ByVal dsExcel As DataSet)
        Dim intCounter As Long = 0
        Dim FName As String = String.Empty
        Dim MName As String = String.Empty
        Dim LName As String = String.Empty
        Dim intCount As Integer = 0
        Dim intResult As Boolean
        Dim intRes As Long = 0
        Dim strCPerson() As String
        Dim objMfg As New CManufacturer
        Dim bytmsg As Byte = 0
        Dim blnchk As Boolean = False
        Dim Status As String = "Status"

        Try
            frmPrg.SetProgress(7)
            If dsExcel.Tables(0).Rows.Count > 0 Then
                dsExcel.Tables(0).Columns.Add(Status)
                For intCounter = 0 To dsExcel.Tables(0).Rows.Count - 1
                    Dim strMName As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(0).ToString())
                    Dim strSName As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(1).ToString())
                    'ReDim strCPerson(dsExcel.Tables(0).Rows(intCounter).Item(2).ToString().Length)
                    If dsExcel.Tables(0).Rows(intCounter).Item(2).ToString() <> String.Empty Then
                        strCPerson = dsExcel.Tables(0).Rows(intCounter).Item(2).ToString.Split(" ")
                    End If

                    If strMName = String.Empty Or strSName = String.Empty Or dsExcel.Tables(0).Rows(intCounter).Item(2).ToString() = String.Empty Then
                        'sbDiscard.Append(intCounter + 1 & ",")
                        dsExcel.Tables(0).Rows(intCounter).Item(3) = "Fail-Either one or more fields are empty"
                    Else
                        With objMfg
                            '.ManufacturerID = 16
                            .SeqManufacturerID = BCommon.GetSequentialID("MFR")
                            .Name = Trim(strMName)
                            If Trim(strSName).Length > 6 Then
                                .ShortName = Trim(strSName).Substring(0, 6)
                            Else
                                .ShortName = Trim(strSName)
                            End If
                            .Description = "ExcelData"
                        End With
                        With objMfg.Person
                            .Title = ConfigurationManager.AppSettings("Title").ToString()
                            FName = String.Empty : MName = String.Empty : LName = String.Empty
                            For intCount = 0 To strCPerson.Length - 1
                                If intCount = 0 Then
                                    FName = strCPerson(intCount)
                                ElseIf intCount = 1 Then
                                    If strCPerson.Length = 3 Then
                                        MName = strCPerson(intCount)
                                    Else
                                        LName = strCPerson(intCount)
                                    End If
                                ElseIf intCount = 2 Then
                                    LName = strCPerson(intCount)
                                End If
                            Next
                            .First_Name = IIf(IsNothing(FName), "", Trim(FName))
                            .Middle_Name = IIf(IsNothing(MName), "", Trim(MName))
                            .Last_Name = IIf(IsNothing(LName), "", Trim(LName))
                            .Display_Name = IIf(IsNothing(FName), "", Trim(dsExcel.Tables(0).Rows(intCounter).Item(2).ToString()))
                        End With

                        intRes = BManufacturer.DetectManufacturer(strMName, objMfg.ShortName)
                        If intRes > 0 Then
                            If Rdoupdate.Checked = True Then
                                objMfg.ManufacturerID = intRes
                                objMfg.SeqManufacturerID = "M" & intRes
                                blnchk = BManufacturer.SaveManufacturer(objMfg, "U", Nothing)
                                If blnchk = True Then bytmsg = 1 : dsExcel.Tables(0).Rows(intCounter).Item(3) = "Success" 'sbUpdate.Append(intCounter + 1 & ",")
                            Else
                                'sbDiscard.Append(intCounter + 1 & ",")
                                dsExcel.Tables(0).Rows(intCounter).Item(3) = "Fail-Imported data discarded"
                                Continue For
                            End If
                        Else : intResult = BManufacturer.SaveManufacturer(objMfg, "S", Nothing)
                            If intResult = True Then dsExcel.Tables(0).Rows(intCounter).Item(3) = "Success" Else dsExcel.Tables(0).Rows(intCounter).Item(3) = "Fail-Data not saved"
                        End If
                    End If
                Next
                frmPrg.SetProgress(8)
                frmPrg.SetProgress(10)
                frmPrg.Hide()
                'Call ReadExcelData()
                Dim FrmMasterDataPreview As New frmMasterPreview
                With FrmMasterDataPreview
                    '.strFilePath = Trim(txtFilePath.Text.ToString())
                    .strPrevSheet = Trim(cmbSheet.SelectedItem.ToString())
                    .dsPrevRecord = dsExcel
                    .ShowDialog()
                End With

                'If IsNothing(sbDiscard) Then
                '    If intResult = True OrElse bytmsg = 1 Then BCommon.ShowMsgError(3) Else BCommon.ShowMsgError(4)
                'Else
                '    strMsg = BCommon.GetMessage(10427)
                '    strMsg = Replace(strMsg, "@ROWNUM", sbDiscard.Remove(sbDiscard.Length - 1, 1).ToString())
                '    If Not IsNothing(sbUpdate) Then strMsg = strMsg & vbCrLf & "And Data Saved successfully from these Row:-" & sbUpdate.Remove(sbUpdate.Length - 1, 1).ToString()
                '    BCommon.ShowMessage(strMsg)
                'End If
                dsExcel = Nothing
                Call ClearData()

            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "StoreManufacturer")
        End Try
    End Sub

    'Store the ExcelSheet Data Into the SQL DATABASE
    Private Sub StoreVendor(ByVal dsExcel As DataSet)
        Dim intCounter As Long = 0
        Dim FName As String = String.Empty
        Dim MName As String = String.Empty
        Dim LName As String = String.Empty
        Dim isStrValid As String = String.Empty
        Dim intCount As Integer = 0
        Dim intResult As Boolean
        Dim strCPerson() As String
        ''Dim ObjAddress(0) As CAddress
        Dim objVendor As New CVendor
        Dim intRes As Long = 0
        Dim bytmsg As Byte = 0
        Dim blnchk As Boolean = False
        Dim sbDiscard As StringBuilder = New StringBuilder()
        Dim Status As String = "Status"

        Try
            frmPrg.SetProgress(7)
            If dsExcel.Tables(0).Rows.Count > 0 Then
                dsExcel.Tables(0).Columns.Add(Status)
                For intCounter = 0 To dsExcel.Tables(0).Rows.Count - 1
                    Dim strVName As String = dsExcel.Tables(0).Rows(intCounter).Item(0).ToString()
                    Dim strVCategory As String = dsExcel.Tables(0).Rows(intCounter).Item(1).ToString()
                    If dsExcel.Tables(0).Rows(intCounter).Item(2).ToString() <> "" Then
                        strCPerson = dsExcel.Tables(0).Rows(intCounter).Item(2).ToString.Split(" ")
                    End If
                    Dim strFDA As String = dsExcel.Tables(0).Rows(intCounter).Item(3).ToString()
                    Dim strLST As String = dsExcel.Tables(0).Rows(intCounter).Item(4).ToString()
                    Dim strCST As String = dsExcel.Tables(0).Rows(intCounter).Item(5).ToString()

                    If strVName = String.Empty OrElse strVCategory = String.Empty OrElse dsExcel.Tables(0).Rows(intCounter).Item(2).ToString() = String.Empty _
                    OrElse strLST = String.Empty OrElse strCST = String.Empty OrElse strFDA = String.Empty Then
                        'sbDiscard.Append(intCounter + 1 & ",")
                        dsExcel.Tables(0).Rows(intCounter).Item(6) = "Fail-Either one or more fields are empty"
                    Else
                        'Assigning the Vendor Object for Saving the Record.
                        With objVendor
                            .VendorID = 0
                            .SeqVendorID = "V"
                            .VendorName = Trim(strVName)
                            isStrValid = BManufacturer.GetMiscCodeID("VENDORCAT", IIf(Trim(strVCategory) = Nothing Or strVCategory = "", "Supplier", strVCategory))
                            If Not IsNothing(isStrValid) Then
                                .VendorCat = isStrValid
                            Else
                                .VendorCat = "S"
                            End If
                            .RegisteredName = ""
                            .FDANUM = Trim(strFDA)
                            .LSTNUM = Trim(strLST)
                            .CSTNUM = Trim(strCST)
                            .PANNUM = ""
                            .PayMode = "1"
                            .DeliveryTime = 1
                            .CreditPeriod = ""
                            .Remark = "VendorExcelSheet_Test"
                        End With
                        With objVendor.PersonID
                            .Title = ConfigurationManager.AppSettings("Title").ToString()
                            FName = String.Empty : MName = String.Empty : LName = String.Empty
                            For intCount = 0 To strCPerson.Length - 1
                                If intCount = 0 Then
                                    FName = strCPerson(intCount)
                                ElseIf intCount = 1 Then
                                    IIf(strCPerson.Length = 3, MName = strCPerson(intCount), LName = strCPerson(intCount))
                                ElseIf intCount = 2 Then
                                    LName = strCPerson(intCount)
                                End If
                            Next
                            .First_Name = IIf(IsNothing(FName), "", Trim(FName))
                            .Middle_Name = IIf(IsNothing(MName), "", Trim(MName))
                            .Last_Name = IIf(IsNothing(LName), "", Trim(LName))
                        End With

                        intRes = BManufacturer.DetectVendor(strVName, strFDA, strLST, strCST)
                        If intRes > 0 Then
                            If Rdoupdate.Checked = True Then
                                objVendor.VendorID = intRes
                                objVendor.SeqVendorID = "V" & intRes
                                blnchk = BVendor.Save(objVendor, "U", Nothing)
                                If blnchk = True Then bytmsg = 1 : dsExcel.Tables(0).Rows(intCounter).Item(6) = "Success" 'sbUpdate.Append(intCounter + 1 & ",")
                            Else
                                'sbDiscard.Append(intCounter + 1 & ",")
                                dsExcel.Tables(0).Rows(intCounter).Item(6) = "Fail-Imported data discarded"
                                Continue For
                            End If
                        Else : intResult = BVendor.Save(objVendor, "S", Nothing)
                            If intResult = True Then dsExcel.Tables(0).Rows(intCounter).Item(6) = "Success" Else dsExcel.Tables(0).Rows(intCounter).Item(6) = "Fail-Data not saved"
                        End If
                    End If
                Next
                frmPrg.SetProgress(8)
                frmPrg.SetProgress(10)
                frmPrg.Hide()

                Dim FrmMasterDataPreview As New frmMasterPreview
                With FrmMasterDataPreview
                    ' .strFilePath = Trim(txtFilePath.Text.ToString())
                    .strPrevSheet = Trim(cmbSheet.SelectedItem.ToString())
                    .dsPrevRecord = dsExcel
                    .ShowDialog()
                End With
                dsExcel = Nothing
                Call ClearData()

            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "StoreVendor")
        End Try
    End Sub

    'Store the ExcelSheet Data Into the SQL DATABASE
    Private Sub StoreItemMaster(ByVal dsExcel As DataSet)
        Dim intCounter As Integer = 0
        Dim dtMFRId As New System.Data.DataTable
        Dim intResult As Boolean
        Dim objItem As New CItem
        Dim isStrValid As String = String.Empty
        Dim intRes As Long = 0
        Dim bytmsg As Byte = 0
        Dim blnchk As Boolean = False
        Dim Status As String = "Status"

        Try
            frmPrg.SetProgress(7)
            If dsExcel.Tables(0).Rows.Count > 0 Then
                dsExcel.Tables(0).Columns.Add(Status)
                For intCounter = 0 To dsExcel.Tables(0).Rows.Count - 1
                    Dim strItem As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(0).ToString())
                    Dim strCompany As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(1).ToString())
                    Dim strMFRName As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(2).ToString())
                    Dim strType As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(3).ToString())
                    Dim strForm As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(4).ToString())
                    Dim strPackType As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(5).ToString())
                    Dim strPackUnit As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(6).ToString())
                    Dim strLocation As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(7).ToString())
                    Dim intItemPerPack As Double = IIf(IsDBNull(dsExcel.Tables(0).Rows(intCounter).Item(8)), 0.0, dsExcel.Tables(0).Rows(intCounter).Item(8))

                    'Assigning the Vendor Object for Saving the Record.
                    If strItem = "" OrElse strCompany = "" OrElse strMFRName = "" OrElse strType = "" OrElse strForm = "" OrElse _
                    strPackType = "" OrElse strPackUnit = "" OrElse strLocation = "" OrElse intItemPerPack = 0.0 Then
                        'sbDiscard.Append(intCounter + 1 & ",")
                        dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail-Either one or more fields are empty"
                    Else
                        With objItem
                            .ItemID = 0
                            .SeqItemID = "I"
                            '.ItemName = IIf(IsNothing(Trim(strItem)), "", Trim(strItem))
                            .ItemName = strItem & " " & strPackType & " " & intItemPerPack & "-" & strPackUnit
                            .ShortName = IIf(IsNothing(Trim(strItem)), "", Trim(strItem))

                            isStrValid = BManufacturer.GetMiscCodeID("ITEM_TYPE", IIf(Trim(strType) = Nothing Or strType = "", "Medicine", strType))
                            .ItemType = IIf(IsNothing(isStrValid) = False, isStrValid.ToString, "MEDICINE")
                            '.ItemType = IIf(IsNothing(Trim(strType)), "", Trim(strType))

                            .ItemCategoryID = 0
                            .BarCode = ""
                            .ItemGrade = "0"
                            .GenericName = "0"
                            .ItemsPerPack = intItemPerPack

                            'Set Company Id From Manufacturer Table

                            dtMFRId = BManufacturer.GetManufacturerID(strCompany)
                            '.CompanyId = IIf(dtMFRId.Rows.Count > 0, dtMFRId.Rows(0).Item(0).ToString(), 1)
                            ''If dtMFRId.Rows.Count > 0 Then .CompanyId = dtMFRId.Rows(0).Item(0).ToString() Else .CompanyId = 1
                            ''If Not dtMFRId.Rows.Count > 0 Then strRowNum = strRowNum & intCounter + 1 & "," : Continue For Else .CompanyId = dtMFRId.Rows(0).Item(0)
                            'If Not dtMFRId.Rows.Count > 0 Then sbDiscard.Append(intCounter + 1 & ",") : Continue For Else .CompanyId = dtMFRId.Rows(0).Item(0)
                            If Not dtMFRId.Rows.Count > 0 Then dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail-Company does not exist" : Continue For Else .CompanyId = dtMFRId.Rows(0).Item(0)

                            'Set Manufacturer Id From Manufacturer Table
                            dtMFRId = BManufacturer.GetManufacturerID(strMFRName)
                            '.ManufacturedID = IIf(dtMFRId.Rows.Count > 0, dtMFRId.Rows(0).Item(0), 1)
                            ''If dtMFRId.Rows.Count > 0 Then .ManufacturedID = dtMFRId.Rows(0).Item(0) Else .ManufacturedID = 1
                            'If Not dtMFRId.Rows.Count > 0 Then sbDiscard.Append(intCounter + 1 & ",") : Continue For Else .ManufacturedID = dtMFRId.Rows(0).Item(0)
                            If Not dtMFRId.Rows.Count > 0 Then dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail-Manufacturer does not exist" : Continue For Else .ManufacturedID = dtMFRId.Rows(0).Item(0)

                            'Set Itemform Id From Miscllenious Table
                            isStrValid = BManufacturer.GetMiscCodeID("ITEMFORM", IIf(Trim(strForm) = Nothing Or strForm = "", "Balm", strForm))
                            .ItemForm = IIf(IsNothing(isStrValid) = False, isStrValid.ToString, "BLM")

                            'Set PurPackingType From Miscllenious Table                  
                            isStrValid = BManufacturer.GetMiscCodeID("PURPACK", IIf(Trim(strPackType) = Nothing Or strPackType = "", "Ampule", strPackType))
                            '.PurPackType = IIf(IsNothing(isStrValid) = False, isStrValid.ToString, "Amp")
                            If IsNothing(isStrValid) = True Then dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail" : Continue For Else .PurPackType = isStrValid.ToString()

                            'Set PurchaseUnit and ItemUnit From Miscllenious Table
                            isStrValid = BManufacturer.GetMiscCodeID("SALEUNIT", IIf(Trim(strPackUnit) = Nothing Or strPackUnit = "", "CC", strPackUnit))
                            .PurchaseUnit = IIf(IsNothing(isStrValid) = False, isStrValid.ToString(), "CC")
                            .ItemUnit = IIf(IsNothing(isStrValid) = False, isStrValid.ToString(), "CC")

                            .Scheduletype = ""
                            .SellInLoose = IIf(BCommon.SetDefault("SELL_LOOSE") = "No", False, True)
                            .SellUnit = IIf(.SellInLoose = False, .PurchaseUnit, .PurPackType)

                            .Remark = "ItemExcelSheetTest"
                            'Set Location Id From Manufacturer Table
                            isStrValid = BLocation.GetLocationID(IIf(Trim(strLocation) = Nothing Or strLocation = "", "1-1-1", strLocation))
                            .LocationID = IIf(IsNothing(isStrValid) = False, CInt(isStrValid), 1)

                            .MinQuantity = 0
                            .MaxQuantity = 0
                            .BarCode = ""
                            .MinReOrderLevel = 0
                            .MaxReOrderQty = 0
                            .MaxDiscount = 0
                            .Margin = 0
                            .Emergency = 0
                            ' >> End
                            .VendorID1 = 0
                            .VendorID2 = 0
                            .VendorID3 = 0
                            .Action1 = ""
                            .Action2 = ""
                            .Action3 = ""
                        End With

                        intRes = BManufacturer.DetectItem(strItem & " " & strPackType & " " & intItemPerPack & "-" & strPackUnit)

                        If intRes > 0 Then
                            If Rdoupdate.Checked = True Then
                                objItem.ItemID = intRes
                                objItem.SeqItemID = "I" & intRes
                                blnchk = DItem.SaveExcelItem(objItem, "U")
                                If blnchk = True Then bytmsg = 1 : dsExcel.Tables(0).Rows(intCounter).Item(9) = "Success" 'sbUpdate.Append(intCounter + 1 & ",")
                            Else
                                'sbDiscard.Append(intCounter + 1 & ",")
                                dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail-Imported data discarded"
                                Continue For
                            End If
                        Else : intResult = DItem.SaveExcelItem(objItem, "S")
                            If intResult > 0 Then dsExcel.Tables(0).Rows(intCounter).Item(9) = "Success" Else dsExcel.Tables(0).Rows(intCounter).Item(9) = "Fail-Data not saved"
                            'sbUpdate.Append(intCounter + 1 & ",")
                        End If

                        'intResult = DItem.SaveExcelItem(objItem, "S")

                    End If
                Next
                frmPrg.SetProgress(8)
                frmPrg.SetProgress(10)
                'frmPrg.Close()
                frmPrg.Hide()

                Dim FrmMasterDataPreview As New frmMasterPreview
                With FrmMasterDataPreview
                    '  .strFilePath = Trim(txtFilePath.Text.ToString())
                    .strPrevSheet = Trim(cmbSheet.SelectedItem.ToString())
                    .dsPrevRecord = dsExcel
                    .ShowDialog()
                End With

                'If IsNothing(sbDiscard) Then
                '    If intResult = True OrElse bytmsg = 1 Then BCommon.ShowMsgError(3) Else BCommon.ShowMsgError(4)
                'Else
                '    strMsg = BCommon.GetMessage(10427)
                '    strMsg = Replace(strMsg, "@ROWNUM", sbDiscard.Remove(sbDiscard.Length - 1, 1).ToString())
                '    If Not IsNothing(sbUpdate) Then strMsg = strMsg & vbCrLf & "And Data Saved successfully from these Row:-" & sbUpdate.Remove(sbUpdate.Length - 1, 1).ToString()
                '    BCommon.ShowMessage(strMsg)
                'End If
                dsExcel = Nothing
                Call ClearData()

            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "StoreItemMaster")
        End Try
    End Sub

    'Store the Opening Stock ExcelSheet Data Into the OpeningStock table in SQL DATABASE
    Private Sub StoreOpenStock(ByVal dsExcel As DataSet)
        Dim intCounter As Integer = 0
        Dim dtbVItems As New System.Data.DataTable
        Dim intResult As Integer
        Dim objBillItem() As CPurchaseItem
        Dim isIdValid As Long = 0
        Dim isVendorValid As Long = 0
        Dim ObjCount As Integer = 0
        Dim Status As String = "Status"

        Try
            frmPrg.SetProgress(7)
            If dsExcel.Tables(0).Rows.Count > 0 Then
                ReDim objBillItem(dsExcel.Tables(0).Rows.Count - 1)
                dsExcel.Tables(0).Columns.Add(Status)
                For intCounter = 0 To dsExcel.Tables(0).Rows.Count - 1
                    Dim strItem As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(0).ToString())
                    Dim strBatch As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(1).ToString())
                    Dim strMFRDt As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(2).ToString())
                    Dim strExpDt As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(3).ToString())
                    Dim strQty As Integer = IIf(IsDBNull(dsExcel.Tables(0).Rows(intCounter).Item(4)), 0, dsExcel.Tables(0).Rows(intCounter).Item(4))
                    Dim dobMRP As Double = IIf(IsDBNull(dsExcel.Tables(0).Rows(intCounter).Item(5)), 0.0, dsExcel.Tables(0).Rows(intCounter).Item(5))
                    Dim dobPurRate As Double = IIf(IsDBNull(dsExcel.Tables(0).Rows(intCounter).Item(6)), 0.0, dsExcel.Tables(0).Rows(intCounter).Item(6))
                    Dim strVendor As String = Trim(dsExcel.Tables(0).Rows(intCounter).Item(7).ToString())
                    'Assigning the Vendor Object for Saving the Record.

                    'If IsNothing(Trim(strItem)) OrElse Trim(strItem) = "" OrElse IsNothing(Trim(strVendor)) OrElse Trim(strVendor) = "" Then
                    If IsNothing(strItem) OrElse Trim(strItem) = String.Empty OrElse _
                       IsNothing(strBatch) OrElse Trim(strBatch) = String.Empty OrElse _
                       IsNothing(strMFRDt) OrElse Trim(strMFRDt) = String.Empty OrElse _
                       IsNothing(strQty) OrElse Trim(strQty) = 0 OrElse _
                       IsNothing(dobMRP) OrElse Trim(dobMRP) = 0 OrElse _
                       IsNothing(dobPurRate) OrElse Trim(dobPurRate) = 0 OrElse _
                       IsNothing(strVendor) OrElse Trim(strVendor) = String.Empty Then
                        'sbDiscard.Append(intCounter + 1 & ",")
                        dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Either one or more fields are empty"
                    Else
                        objBillItem(ObjCount) = New CPurchaseItem
                        With objBillItem(ObjCount)
                            isIdValid = DItem.GetItemIDByItem(strItem)
                            'Check the Item is Present in the System or not? (Means in mpItem table)
                            'If isIdValid > 0 Then .ItemID = isIdValid Else  sbDiscard.Append(intCounter + 1 & ",") : Continue For
                            If isIdValid > 0 Then .ItemID = isIdValid Else dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Item name dose not exist" : Continue For

                            'Check Opening stock for this item is already done.(Means Record Found in tOpeningStock table)
                            If BPurchaseInvoice.IsStockAvail(isIdValid, strBatch) > 0 Then dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Stock is allready opened for the same item having same batch" : Continue For

                            isVendorValid = BVendor.GetVendorIdByName(Trim(strVendor))

                            If isVendorValid <= 0 Then dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Vendor does not exist" : Continue For

                            .BatchNo = strBatch
                            .ManufactureDate = CType(strMFRDt, Date)
                            .ExpiryDate = CType(strExpDt, Date)

                            If .ManufactureDate > Date.Today Then
                                dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Manufacturer date is greater than current date" : Continue For
                            Else
                                .ManufactureDate = BCommon.GetDatewithLastDay(.ManufactureDate)
                            End If

                            If Date.Today > .ExpiryDate Then
                                dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Expiry date is less than current date" : Continue For
                            Else
                                .ExpiryDate = BCommon.GetDatewithLastDay(.ExpiryDate)
                            End If
                            .Quantity = IIf(IsNothing(strQty), 0, strQty)
                            .StockQuantity = IIf(IsNothing(strQty), 0, strQty)
                            .MRP = IIf(IsNothing(dobMRP), 0.0, dobMRP)
                            .BillRate = IIf(IsNothing(dobPurRate), 0.0, dobPurRate)
                            .PurchasePrice = IIf(IsNothing(dobPurRate), 0.0, dobPurRate)
                            .Net_Rate = IIf(IsNothing(dobPurRate), 0.0, dobPurRate)
                            .SalePrice1 = 0.0
                            'Items which r not till supplied by by that vendor but now started suppling new item
                            dtbVItems = DItem.GetValidVendor(isVendorValid, .ItemID)
                            If dtbVItems.Rows.Count <= 0 Then
                                Dim intManufacurer As Integer = BManufacturer.GetManufacturer(.ItemID)
                                intResult = DItem.SaveItemsMV(intManufacurer, isVendorValid, .ItemID)
                                If intResult = 0 Then BCommon.ShowMsgError(4) : Exit Sub
                            End If
                        End With
                        ObjCount = ObjCount + 1
                        intResult = BPurchaseInvoice.SaveOpeningStock(objBillItem, isVendorValid)
                        If intResult > 0 Then dsExcel.Tables(0).Rows(intCounter).Item(8) = "Success" Else dsExcel.Tables(0).Rows(intCounter).Item(8) = "Fail-Data not saved"
                    End If
                Next
                frmPrg.SetProgress(8)
                frmPrg.SetProgress(10)
                frmPrg.Hide()

                Dim FrmMasterDataPreview As New frmMasterPreview
                With FrmMasterDataPreview
                    ' .strFilePath = Trim(txtFilePath.Text.ToString())
                    .strPrevSheet = Trim(cmbSheet.SelectedItem.ToString())
                    .dsPrevRecord = dsExcel
                    .ShowDialog()
                End With

                dsExcel = Nothing
                'If sbDiscard.Length > 0 Then
                '    strMsg = BCommon.GetMessage(10427)
                '    strMsg = Replace(strMsg, "@ROWNUM", sbDiscard.Remove(sbDiscard.Length - 1, 1).ToString())
                '    If sbUpdate.Length > 0 Then strMsg = strMsg & vbCrLf & "And Data Saved successfully from these Row:-" & sbUpdate.Remove(sbUpdate.Length - 1, 1).ToString()
                '    BCommon.ShowMessage(strMsg)
                'Else
                '    If sbUpdate.Length > 0 Then strMsg = "Data saved successfully from these Row:- " & sbUpdate.Remove(sbUpdate.Length - 1, 1).ToString()
                '    BCommon.ShowMessage(strMsg)
                'End If
                dsExcel = Nothing
                Call ClearData()

            End If
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "StoreItemMaster")
            dsExcel = Nothing
        End Try
    End Sub

    Private Sub ClearData()
        Try
            Me.cmbSheet.Items.Clear()
            Me.txtFilePath.Text = String.Empty
            Me.Rdodiscard.Checked = True
            Me.btnPreview.Enabled = False
        Catch ex As Exception
            BCommon.ErrorHandler(ex.Message, Me.ProductName, Me.Name, "ClearData")
        End Try
    End Sub

#End Region

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Private Sub cmbSheet_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cmbSheet.KeyUp
        If Trim(cmbSheet.Text) = "OpeningStock" Then Rdoupdate.Enabled = False Else Rdoupdate.Enabled = True
    End Sub
End Class